﻿using Common;
using DataLibrary;
using LinqKit;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace TicketSystem
{
    public partial class Form_Manager : Form
    {
        private TicketSystemEntities tse = new TicketSystemEntities();

        //private DataTable dt = new DataTable();
        public Form_Manager()
        {
            InitializeComponent();            
        }

        private void Form_Manager_Load(object sender, EventArgs e)
        {
            InitData();
        }

        private void cb_All_CheckedChanged(object sender, EventArgs e)
        {
            var controls = groupBox2;
            foreach (Control ctl in controls.Controls)
            {
                CheckBox cb = ctl as CheckBox;
                if (cb_All.Checked == true)
                {
                    if (!cb.Checked)
                        cb.Checked = true;
                }
                else
                {
                    if (cb.Checked)
                        cb.Checked = false;
                }                
            }
        }
    
        private void btn_Search_Click(object sender, EventArgs e)
        {
            string where = GetWhere();
            try
            {
                BindData(where);
                ColSection();
            }
            catch (Exception ex)
            {
                MessageBox.Show("检索出错:" + ex.Message);
            }
        }

        /// <summary>
        /// 计算一天内的出票，退票汇总
        /// </summary>
        /// <param name="saleList"></param>
        /// <param name="backList"></param>
        /// <returns></returns>
        private SummaryModel Calculate(List<Sales> saleList,List<Backs> backList)
        {
            DataTable dt = GetTable();
            SummaryModel sm = new SummaryModel();
            
            foreach(var sale in saleList)
            {
                sm.TicketOne += sale.TicketOne;
                sm.TicketTwo += sale.TicketTwo;
                sm.TicketThree += sale.TicketThree;
                sm.TicketParkAdult += sale.TicketPark;
                sm.TicketParkSpecial += sale.TicketParkSpecial;
                sm.TicketSigned += sale.SignedPeople;
                
                sm.OneMoney += Price.PriceOne * sale.TicketOne;
                sm.TwoMoney += Price.PriceTwo * sale.TicketTwo;
                sm.ThreeMoney += Price.PriceThree * sale.TicketThree;
                sm.ParkAdultMoney += Price.PriceParkAdult * sale.TicketPark;
                sm.ParkSpecialMoney += Price.PriceParkSpecial * sale.TicketParkSpecial;
                sm.SignedMoney += sale.SignedMoney;
                sm.PurchaseMoney += sale.PurchaseMoney;                
            }
            sm.TicketUnSigned = sm.TicketOne - sm.TicketSigned;
            sm.SaleTotalMoney = sm.PurchaseMoney + sm.SignedMoney;
            foreach(var back in backList)
            {
                sm.BackOne += back.BackOne;
                sm.BackTwo += back.BackTwo;
                sm.BackThree += back.BackThree;
                sm.BackParkAdult += back.BackPark;
                sm.BackParkSpecial += back.BackParkSpecial;
                sm.BackOneMoney += Price.PriceOne * back.BackOne;
                sm.BackTwoMoney += Price.PriceTwo * back.BackTwo;
                sm.BackThreeMoney += Price.PriceThree * back.BackThree;
                sm.BackParkAdultMoney += Price.PriceParkAdult * back.BackPark;
                sm.BackParkSpecialMoney += Price.PriceParkSpecial * back.BackParkSpecial;
                sm.BackTotalMoney += back.BackMoney;
            }
            //sm.BackTotalMoney = sm.BackOneMoney + sm.BackTwoMoney + sm.BackThreeMoney + sm.BackParkAdultMoney + sm.BackParkSpecialMoney;//退款金额这里存在问题？？
            return sm;
        }

        /// <summary>
        /// 将检索结果呈现，where：检索条件
        /// </summary>
        /// <param name="where"></param>
        private void BindData(string where)
        {
            string where1 = where.Split('|')[0];
            string where2 = where.Split('|')[1];
            var sales = tse.Sales.SqlQuery(where1).AsEnumerable();
            var backs = tse.Backs.SqlQuery(where2).AsEnumerable();
            DateTime start = Convert.ToDateTime(dTP_Start.Text + " 00:00:00");
            DateTime end = Convert.ToDateTime(dTP_End.Text + " 23:59:59");
            TimeSpan sp = end.Subtract(start);
            int days = sp.Days + 1;
            if (days == 0)
                days = 1;
            DataTable NewDt = GetTable();
            for (int i = 0; i < days; i++)
            {               
                DateTime StartOfOneday = start.AddDays(i);
                DateTime EndOfOneDay = start.AddDays(i + 1);
                List<Sales> SalesOfOneday = new List<Sales>();
                List<Backs> BacksOfOneday = new List<Backs>();                
                if (checkBox_CheckOut.Checked)
                {
                    SalesOfOneday = sales.Where(u => u.Checkout_at >= StartOfOneday && u.Checkout_at < EndOfOneDay).ToList();
                }
                else
                {
                    SalesOfOneday = sales.Where(u => u.Created_at >= StartOfOneday && u.Created_at < EndOfOneDay).ToList();
                }
                BacksOfOneday = backs.Where(u => u.Created_at > StartOfOneday && u.Created_at < EndOfOneDay).ToList();
                SummaryModel sm = Calculate(SalesOfOneday, BacksOfOneday);
                sm.SaleDepartTotal = SalesOfOneday.Count();
                sm.BackDepartTotal = BacksOfOneday.Count();
                string _start = StartOfOneday.ToString();
                string _end = EndOfOneDay.ToString();
                if (checkBox_TimeSpan.Checked)
                {
                    _start = _start.Replace("0:00:00", "") + dTP_StartTime.Text + "-" + dTP_EndTime.Text;
                    _end = _end.Replace("0:00:00", "") + dTP_StartTime.Text + "-" + dTP_EndTime.Text;
                }
                NewDt.Rows.Add(_start, _end, sm.TicketOne, sm.OneMoney, sm.TicketParkAdult, sm.ParkAdultMoney, sm.TicketParkSpecial, sm.ParkSpecialMoney, sm.TicketTwo, sm.TwoMoney,
                    sm.TicketThree, sm.ThreeMoney, sm.TicketSigned, sm.SignedMoney, sm.TicketUnSigned, sm.PurchaseMoney, sm.SaleTotalMoney, sm.SaleDepartTotal, sm.BackOne, sm.BackOneMoney, sm.BackParkAdult,
                    sm.BackParkSpecial, sm.BackParkAdultMoney + sm.BackParkSpecialMoney, sm.BackTwo, sm.BackTwoMoney, sm.BackThree, sm.BackThreeMoney, sm.BackTotalMoney, sm.BackDepartTotal);
            }
            dataGridView1.DataSource = NewDt;
            //dataGridView1.AutoGenerateColumns = false;
            //dataGridView1.Columns[0].Frozen = true;
            //dataGridView1.Columns[1].Frozen = true;
            
        }
        //恩，没错，没有用ef，就是拼的sql，主要有个时间段不知道咋用ef弄~~
        private string GetWhere()
        {
            string timetype = "Created_at";
            if (checkBox_CheckOut.Checked)
                timetype = "Checkout_at";
            string start = dTP_Start.Text + " 00:00:00";
            string end = dTP_End.Text + " 23:59:59";
            string where = string.Format("select * from Sales where {0} >= '{1}' and {0}<='{2}'", timetype, start, end);
            string whereB = string.Format("select * from Backs where Created_at >= '{0}' and Created_at <= '{1}'", start, end);
            if (checkBox_TimeSpan.Checked)
            {
                string starttime = dTP_StartTime.Text;
                string endtime = dTP_EndTime.Text;
                where += string.Format(" and convert(char(8),{0},108)>='{1}' and convert(char(8),{0},108)<='{2}'", timetype, starttime, endtime);
                //退票的检索条件直接吧日期条件改掉即可
                whereB = string.Format("select * from Backs where Created_at >= '{0}' and Created_at <= '{1}'", dTP_Start.Text + " " + starttime, dTP_End.Text + " " + endtime);
            }
            if (!string.IsNullOrEmpty(cBox_Operator.Text))
            {
                int operatorid = (int)cBox_Operator.SelectedValue;
                where += string.Format(" and OperatorID={0}", operatorid);
                whereB += string.Format(" and OperatorID={0}", operatorid);
            }
            if (!string.IsNullOrEmpty(cBox_Depart.Text))
            {
                int departid = tse.Departments.Where(u => u.DepartmentName == cBox_Depart.Text).First().DepartmentID;
                where += string.Format(" and DGID in (select DGID from DepartmentsGuides where DepartmentID = {0})", departid);
                whereB += string.Format(" and SaleID in (select SaleID from Sales where DGID in (select DGID from DepartmentsGuides where DepartmentID = {0}))", departid);
            }
            if (!string.IsNullOrEmpty(cBox_Guide.Text))
            {
                int guideid = tse.Guides.Where(u => u.GuideCard == cBox_Guide.Text).First().GuideID;
                where += string.Format(" and DGID in (select DGID from DepartmentsGuides where GuideID = {0})", (int)cBox_Guide.SelectedValue);
                whereB += string.Format(" and SaleID in (select SaleID from Sales where DGID in (select DGID from DepartmentsGuides where GuideID = {0}))", (int)cBox_Guide.SelectedValue);
            }
            switch (cBox_TicketType.Text)
            {
                case "全部":
                    break;
                case "签单":
                    where += string.Format(" and SignedMoney > 0 and BackFlag = 0");
                    break;
                case "非签单":
                    where += string.Format(" and PurchaseMoney > 0 and BackFlag = 0");
                    break;
                default:
                    break;
            }
            return where + "|" + whereB;
        }

        

        private DataTable GetTable(){
            DataTable dtRet = new DataTable();
            //dtRet.Columns.Add("统计类型");
            dtRet.Columns.Add("开始时间");
            dtRet.Columns.Add("结束时间");
            dtRet.Columns.Add("首道船人数");
            dtRet.Columns.Add("首道船金额");
            dtRet.Columns.Add("进园成人数");
            dtRet.Columns.Add("成人票金额");
            dtRet.Columns.Add("进园学生数");
            dtRet.Columns.Add("学生票金额");
            dtRet.Columns.Add("颐内一人数");
            dtRet.Columns.Add("颐内一金额");
            dtRet.Columns.Add("颐内二人数");
            dtRet.Columns.Add("颐内二金额");
            dtRet.Columns.Add("签单人数");
            dtRet.Columns.Add("签单金额");
            dtRet.Columns.Add("非签单人数");
            dtRet.Columns.Add("现付金额");
            dtRet.Columns.Add("出票总金额");
            dtRet.Columns.Add("出票总团数");
            //dtRet.Columns.Add("退票金额");
            dtRet.Columns.Add("首道船退票(含签单)");
            dtRet.Columns.Add("首退金额(含签单)");
            dtRet.Columns.Add("成人退票");
            dtRet.Columns.Add("学生退票");
            dtRet.Columns.Add("入园退金额");
            dtRet.Columns.Add("颐内一退票");
            dtRet.Columns.Add("颐内一退金");
            dtRet.Columns.Add("颐内二退票");
            dtRet.Columns.Add("颐内二退金");
            dtRet.Columns.Add("退票总金额");
            dtRet.Columns.Add("退票总团数");            
            //dtRet.Columns.Add("应收金额");
            //dtRet.Columns.Add("实收金额");
            return dtRet;
        }
        
        private void ColSection()
        {            
            DataTable dt = (DataTable)dataGridView1.DataSource;
            var controls = groupBox2;
            foreach (Control ctl in controls.Controls)
            {
                CheckBox cb = ctl as CheckBox;
                if (cb.Text == "全选" || cb.Text == "时间")
                        continue;
                if (cb.Text == "首道船退票" && !cb.Checked)
                {
                    dt.Columns.Remove("首道船退票(含签单)");
                    continue;
                }
                if (cb.Text == "首退金额" && !cb.Checked)
                {
                    dt.Columns.Remove("首退金额(含签单)");
                    continue;
                }
                if (!cb.Checked)
                {                    
                    dt.Columns.Remove(cb.Text);
                }
            }
            dataGridView1.DataSource = null;//不加这句，再次检索后原来删除的列会跑到最后面
            dataGridView1.DataSource = dt;
        }
        private void InitData()
        {            
            var _opes = tse.Operators.Select(u => new { u.OperatorID, u.Name }).ToList();
            cBox_Operator.DataSource = _opes;
            cBox_Operator.ValueMember = "OperatorID";
            cBox_Operator.DisplayMember = "Name";
            var _OpeSource = new AutoCompleteStringCollection();            
            foreach (var _ope in _opes)
            {
                _OpeSource.Add(_ope.Name + "|" + _ope.OperatorID);
            }
            
            var departments = tse.Departments.Select(u => new { u.DepartmentName,u.DepartmentID }).ToList();
            //cBox_Depart.DataSource = departments;
            //cBox_Depart.ValueMember = "DepartmentID";
            //cBox_Depart.DisplayMember = "DepartmentName";
            var _DepartSourcce = new AutoCompleteStringCollection();
            foreach (var department in departments)
            {
                _DepartSourcce.Add(department.DepartmentName.Trim());
            }
            var guides = tse.Guides.Select(u => new { u.GuideID, u.GuideCard }).ToList();
            //cBox_Guide.DataSource = guides;
            //cBox_Guide.ValueMember = "GuideID";
            //cBox_Guide.DisplayMember = "GuideCard";
            var _GuideSourcce = new AutoCompleteStringCollection();
            foreach (var guide in guides)
            {
                _GuideSourcce.Add(guide.GuideCard);
            }

            cBox_Operator.AutoCompleteCustomSource = _OpeSource;
            cBox_Operator.AutoCompleteMode = AutoCompleteMode.SuggestAppend;
            cBox_Operator.AutoCompleteSource = AutoCompleteSource.CustomSource;
            
            
            cBox_Depart.AutoCompleteCustomSource = _DepartSourcce;
            cBox_Depart.AutoCompleteMode = AutoCompleteMode.SuggestAppend;
            cBox_Depart.AutoCompleteSource = AutoCompleteSource.CustomSource;
            

            cBox_Guide.AutoCompleteCustomSource = _GuideSourcce;
            cBox_Guide.AutoCompleteMode = AutoCompleteMode.SuggestAppend;
            cBox_Guide.AutoCompleteSource = AutoCompleteSource.CustomSource;


            
            this.dataGridView1.DataSource = null;
            cBox_Operator.Text = "";
            cBox_Depart.Text = "";
            cBox_Guide.Text = "";

            //dt = GetTable();
        }

        private void btn_ExportAll_Click(object sender, EventArgs e)
        {
            DataTable dt = (DataTable)dataGridView1.DataSource;
            if (dt == null || dt.Rows.Count == 0)
            {
                MessageBox.Show("无数据");
            }
            else
            {
                System.Web.UI.WebControls.TableCell[] header = new System.Web.UI.WebControls.TableCell[dt.Columns.Count];
                for (int i = 0; i < header.Length; i++)
                {
                    header[i] = new System.Web.UI.WebControls.TableCell();
                    header[i].Text = dt.Columns[i].ColumnName;
                }
                string filename = "";
                saveFileDialog1.FileName = DateTime.Now.ToString("yyyyMMdd-HHmmss") + "-管理报表.xls";
                saveFileDialog1.Filter = " xls files(*.xls)|*.xlsx|All files(*.*)|*.*";
                saveFileDialog1.FilterIndex = 2;
                saveFileDialog1.RestoreDirectory = true;
                if (saveFileDialog1.ShowDialog() == DialogResult.OK)
                {
                    try
                    {
                        filename = saveFileDialog1.FileName.ToString();
                        ToExcel.DataTable2Excel(dt, header, filename, null, 0);
                        MessageBox.Show("导出成功!");
                    }
                    catch(Exception ex)
                    {
                        MessageBox.Show(ex.Message);
                    }
                }               
            }
        }

        private void checkBox_TimeSpan_CheckedChanged(object sender, EventArgs e)
        {
            if (this.checkBox_TimeSpan.Checked == true)
            {
                dTP_StartTime.Enabled = true;
                dTP_EndTime.Enabled = true;
            }
            else
            {
                dTP_StartTime.Enabled = false;
                dTP_EndTime.Enabled = false;
            }
        }

        private void Form_Manager_FormClosed(object sender, FormClosedEventArgs e)
        {
            FormShow.f_manager = false;
        }
    }
}
